（基础篇）SQL 连接

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 3.5k | 阅读时长 ≈ 5 分钟

什么是连接？

  SQL 中的连接（JOIN）语句用于将数据库中的两个或多个表组合起来。由”连接”生成的集合，可以被保存为表，或者当成表来使用。JOIN 语句的含义是把两张表的属性通过它们的值组合在一起。基于 ANSI 标准的 SQL 列出了五种 JOIN 方式：内连接（INNER），全外连接（FULL OUTER），左外连接（LEFT OUTER），右外连接（RIGHT OUTER）和交叉连接（CROSS）。在特定的情况下，一张表（基本表，视图，或连接表）可以和自身进行连接，成为自连接（self-join）。

连接的类型

  连接可以分为以下类型：

连接类型 自连接 内连接 左(外)连接 右(外)连接 全(外)连接 交叉连接
关键字 无 INNER JOIN LEFT (OUTER) JOIN RIGHT (OUTER) JOIN FULL (OUTER) JOIN CROSS JOIN

  常用的连接为内连接，左连接，右连接，自连接，一般用不到全外连接和交叉连接。

所需数据表

  为了表达清楚上面各种连接的区别，需要创建 2 张数据表:
  供货商表vendors信息（主键为vend_id）如下图：

供货商表

  商品表products信息(主键为prod_id，外键为供货商主键vend_id)如下图：
商品表

自连接

  假如你在商品表发现某物品（其prod_id为 20005）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。

  第一种解决方案：
  首先找到生产prod_id为 20005 的物品的供应商，然后找出这个供应商生产的其他物品：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询语句
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 20005);

# 查询结果
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| 20005   | MIX 3     |
| 20006   | NFC       |
+---------+-----------+

  上面使用了子查询。内部的 SELECT 语句做 了一个简单的检索，返回生产prod_id为 20005 的物品供应商的vend_id。该 id 作为外部查询 WHERE 子句的条件，以便检索出这个供应商生 产的所有物品。

  但这样太过繁琐，我们可以通过使用自连接，也将得到上面的结果。
  第二种解决方案：

1
2
3
SELECT p1.prod_id,p1.prod_name
FROM products AS p1,products AS p2
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 20005;

内连接

  内连接：通过把一个数据表里的数据行与另一个数据表里的数据行进行匹配来产生结果。
  内连接使用INNER JOIN关键字连接两表，可以显示左表和右表符合连接条件的记录：

1
2
3
SELECT *
FROM vendors AS v INNER  JOIN  products AS p
ON v.vend_id = p.vend_id;

运行结果

  查询语句中：

  • *：代表从FROM子句所列出的每一个数据表里选取每一个数据列，与vendors.*,products.*等效。
  • ON：与 WHERE等效。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 查询语句
SELECT p.prod_name,p.prod_price,p.prod_desc,v.vend_name
FROM vendors AS v INNER  JOIN  products AS p
ON v.vend_id = p.vend_id;

# 查询结果
+--------------+------------+--------------+-----------+
| prod_name    | prod_price | prod_desc    | vend_name |
+--------------+------------+--------------+-----------+
| 荣耀9999X    |    5999.00 | 华为手机     | 华为      |
| MAC6666      |    8999.00 | MAC电脑      | 苹果      |
| iphone6s     |    4366.00 | 苹果手机     | 苹果      |
| S8 edge      |    3999.00 | 三星手机     | 三星      |
| MIX 3        |    4236.00 | 小米手机     | 小米      |
| NFC          |     888.00 | 智能手环     | 小米      |
| cloud server |     999.00 | 云服务器     | 华为      |
| sony camera  |    3999.00 | 数码相机     | 索尼      |
| Xperia XZ4   |    4666.00 | 索尼手机     | 索尼      |
+--------------+------------+--------------+-----------+

  如下图：

注意哦：若内连接不添加连接条件，将显示笛卡尔积（见交叉连接），如：

1
2
SELECT *
FROM vendors INNER  JOIN  products;

外连接

  内连接只显示在两个数据表里都能找到匹配的数据行。
  外连接除了显示同样的匹配结果，还可把其中一个数据表在另一数据表里未匹配的数据也显示出来。
  外连接又分为：

  • 左（外）连接：把左表数据在右表中没有匹配的数据行也显示出来；
  • 右（外）连接：把右表数据在左表中没有匹配的数据行也显示出来；

左（外）连接

  显示左表的所有记录，即使在右表中没有匹配的数据行也显示出来。

1
2
3
4
# 查询语句
SELECT *
FROM vendors AS v LEFT  JOIN  products AS p
ON v.vend_id = p.vend_id

查询结果

  从查询结果可以看到供货商英特尔没有提供商品在商品表中。
  抽象化理解如下图：

右（外）连接

  类似于左（外）连接，关键字为RIGHT JOIN，抽象化理解如下图：

全（外）连接（了解）

  显示左表和右表的并集数据，因为 MySQL 不支持该连接（ SQL Server 支持），所以不再演示，抽象化理解如下图：

交叉连接（笛卡尔积）

  由没有连接条件的表关系返回的结果为笛卡儿积：检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
  如对供货商表和商品表使用交叉连接的 sql 语句及查询结果：

1
2
SELECT * 
FROM vendors CROSS JOIN  products
查询结果

  可以看到有许多数据是供货商表和商品表没有的。
  抽象化理解：把这两张表当作一张纸里相交叉的两个圆，数据却居然为整张纸（包括两个圆），如下图：

避免歧义：如何在连接操作中给出数据列的名字

  在SELECT语句里给出的数据列的名字不允许产生歧义，而且必须来自FROM字句的某个数据库。
  若只涉及一个数据表，就不会产生歧义，因为被列出的所有数据列都来自那个数据表。
  若涉及多个数据表，只在一个表中出现的数据列不会产生歧义。但是若某个数据列的名字出现在多个数据表中，在引用这个数据列时必须使用tb1_name1.col_name的语法给它加上一个数据表的名字来表明它来自哪一个数据表。

  有时用数据表的名字进行限定仍不能解决数据列的歧义问题。
  比如自连接操作是在查询命令里多次用到同一个数据表，用数据表的名字来限定数据列没有用。此时应使用表别名，如：

1
2
3
SELECt t1.name,t2.age 
FROM table AS t1 INNER JOIN table AS t2 
WHERE t1.age > t2.age;

特殊过滤：左/右连接下过滤条件

  在使用左连接是，数据的查询结果可能是不一样的，比如说存在下面两段SQL，第一个 SQL 会无法bus_contract_receive_pay_record表中不存在的记录也查询出来，而第二个 SQL 确可以。

1
2
3
4
5
6
7
8
9
10
11
SELECT p.contract_code, p.contract_start, p.contract_end, p.contract_amount, ps.service_project, ps.receive_pay_type,
        psd.receive_pay_date AS planReceivePayDate, psd.receive_pay_amount AS planAmount, pre.receive_pay_amount AS realReceivePayAmount, pre.receive_pay_time
        FROM bus_contract_receive_pay_plan AS p INNER JOIN bus_contract_receive_pay_plan_service_project AS ps
        ON p.id = ps.plan_id INNER JOIN bus_contract_receive_pay_plan_service_project_detail AS psd
        ON ps.id = psd.project_id LEFT JOIN bus_contract_receive_pay_record AS pre
        ON psd.id = pre.plan_detail_id
        WHERE p.contract_type = 1
        AND p.delete_flag = 0
        AND ps.delete_flag = 0
        AND psd.delete_flag = 0
        AND pre.delete_flag = 0;

1
2
3
4
5
6
SELECT p.contract_code, p.contract_start, p.contract_end, p.contract_amount, ps.service_project, ps.receive_pay_type,
        psd.receive_pay_date AS planReceivePayDate, psd.receive_pay_amount AS planAmount, pre.receive_pay_amount AS realReceivePayAmount, pre.receive_pay_time
        FROM bus_contract_receive_pay_plan AS p INNER JOIN bus_contract_receive_pay_plan_service_project AS ps
        ON p.id = ps.plan_id AND p.contract_type = 1 AND p.delete_flag = 0 INNER JOIN bus_contract_receive_pay_plan_service_project_detail AS psd
        ON ps.id = psd.project_id AND ps.delete_flag = 0 AND psd.delete_flag = 0 LEFT JOIN  bus_contract_receive_pay_record AS pre
        ON psd.id = pre.plan_detail_id AND pre.delete_flag = 0;

image-20250523171921605

  那么，这是为什么呢？
  主要原因在于，第一段的 SQL 写法有问题，下面我们分析下：

问题分析

  第一个 SQL 的问题：

1
2
3
4
5
6
7
8
-- 第一个SQL的WHERE条件中包含了 pre.delete_flag = 0
LEFT JOIN bus_contract_receive_pay_record AS pre
ON psd.id = pre.plan_detail_id
WHERE p.contract_type = 1
AND p.delete_flag = 0
AND ps.delete_flag = 0
AND psd.delete_flag = 0
AND pre.delete_flag = 0;  -- 关键问题在这里

  LEFT JOIN 的语义： LEFT JOIN 的目的是保留左表（psd）的所有记录，即使右表（pre）没有匹配的记录，但是前面我们在 WHERE 子句中添加了 pre.delete_flag = 0，这会强制要求 pre 必须存在且满足 delete_flag = 0。

  并且，上述 SQL 进行了隐式转换为 INNER JOIN：当 pre 表中没有匹配记录时，pre.delete_flag 会是 NULL，而 NULL = 0 不成立，因此这些记录会被 WHERE 条件过滤掉。这实际上将 LEFT JOIN 转换为了 INNER JOIN，导致 pre 表不存在的记录被排除。

  第二个 SQL 的改进：

1
2
3
-- 第二个SQL将条件移到ON子句中
LEFT JOIN bus_contract_receive_pay_record AS pre
ON psd.id = pre.plan_detail_id AND pre.delete_flag = 0;  -- 关键改进在这里

  将过滤条件放在 ON 子句中：这里将 pre.delete_flag = 0 移到了 LEFT JOIN 的 ON 条件中。
这意味着：

  • 如果 pre 表存在匹配记录，则仅选择 delete_flag = 0 的记录
  • 如果 pre 表没有匹配记录，仍然保留左表（psd）的记录，此时 pre.*字段为 NULL

  保留左表的所有记录：由于 WHERE 子句中不再包含对 pre 表的过滤条件，即使 pre 表没有匹配记录，左表（psd）的记录也会被保留。

  其他差异：
两个SQL对其他表（p、ps、psd）的过滤条件位置也不同：

  • 第一个SQL 在 WHERE 子句中过滤这些表的 delete_flag，可能导致结果被错误过滤
  • 第二个SQL 将过滤条件放在 JOIN … ON 子句中，确保在连接时提前过滤数据，避免后续影响 LEFT JOIN 的结果。

总结

第一个SQL 因 WHERE 子句中的 pre.delete_flag = 0，导致 LEFT JOIN 失效，结果类似 INNER JOIN。

第二个SQL 将 pre.delete_flag = 0 移到 ON 子句中，正确保留了左表的所有记录，即使 pre 表不存在匹配数据。

参考

  • Ben Forta. MySQL 必知必会 [M]. 人民邮电出版社, 2009
  • Paul DuBois. MySQL 技术内幕 [M]. 人民邮电出版社, 2011

文章信息

时间 说明
2019-02-09 初稿
0%